package com.zzlzd.drp.domain;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface UserRepository extends JpaRepository<UserDto,Integer> {

    @Query(nativeQuery=true, value=" SELECT * FROM user WHERE id = ?1")
    public UserDto findUserById(int id);

    @Query(nativeQuery=true, value=" SELECT * FROM user WHERE userid = ?1")
    public UserDto findUserByUserId(String userid);

    @Query(nativeQuery=true, value=" SELECT * FROM user WHERE phone = ?1 ")
    public UserDto findPhone(String phone);

    @Query(nativeQuery=true, value=" SELECT * FROM user WHERE phone = ?1  AND pwd = ?2")
    public UserDto Login(String phone, String pwd);


    @Query(nativeQuery=true, value=" SELECT * FROM user WHERE phone = ?1")
    public UserDto findByPhone(String phone);


    @Query(nativeQuery=true, value=" SELECT * FROM user WHERE userid = ?1")
    public UserDto isedituserid(String userid);

    @Query(nativeQuery=true, value=" SELECT * FROM user WHERE token = ?1")
    public UserDto getUserByToken(String token);

    @Query(nativeQuery=true, value=" SELECT * FROM user WHERE openid = ?1 LIMIT 1")
    public UserDto findUserByOpenid(String openid);


    @Query(nativeQuery=true, value=" SELECT * FROM user WHERE isedituserid = 0")
    public List<UserDto> sendMsggetUser();

    @Query(nativeQuery=true, value=" SELECT COUNT(id) FROM user WHERE phone = ?1")
    public int getIsPhone(String phone);

    /***
     * 绑定上级查询
     * @param name
     * @param phone
     * @return
     */
    @Query(nativeQuery=true, value=" SELECT * FROM `user` WHERE name = ?1 AND phone = ?2")
    public UserDto findUpUser(String name, String phone);

    /***
     * 会员列表
     * @return
     */
    @Query(nativeQuery=true, value="SELECT u.id ,u.name, u.phone, u.money, u.head,COUNT(u.id) num,  case u.level when 0 then '会员' when 1 then '天使' when 2 then '市代' when 3 then '省代' when 4 then '分公司' else '未知' end levels ,is_admin isAdmin FROM user u LEFT JOIN relation r on r.pid = u.id GROUP BY u.id")
    public List<Map<String,Object>> findUserList();

    /***
     * 下级详情
     * @param id
     * @return
     */
    @Query(nativeQuery=true, value="SELECT u.id id,u.name name,u.head,u.phone,u.money,u.is_up agent,u.is_down down, case u.`level` when 0 then '会员' when 1 then '天使' when 2 then '市代' when 3 then '省代' when 4 then '分公司' else '未知' end  levels ,is_admin isAdmin FROM relation r  LEFT JOIN `user` u on u.id=r.uid WHERE r.pid = ?1 AND r.level = 1 ")
    List<Map<String, Object>> findUserListById(String id);

    @Query(nativeQuery=true, value=" select a.click_date as data,ifnull(b.count,0) as count from ( SELECT curdate() as click_date union all SELECT date_sub(curdate(), interval 1 day) as click_date  union all SELECT date_sub(curdate(), interval 2 day) as click_date union all SELECT date_sub(curdate(), interval 3 day) as click_date union all SELECT date_sub(curdate(), interval 4 day) as click_date union all  SELECT date_sub(curdate(), interval 5 day) as click_date union all SELECT date_sub(curdate(), interval 6 day) as click_date ) a left join ( select date(t.creat) as datetime, count(id) as count from user t   group by date(t.creat)  ) b on a.click_date = b.datetime ORDER BY data")
    List<Map<String, Object>> peopleData();

    @Query(nativeQuery=true, value=" SELECT * FROM `user` WHERE phone = ?1")
    UserDto getUserByTel(String phone);

    @Query(nativeQuery=true, value=" SELECT COUNT(id) FROM `user` WHERE name = ?1")
    int countNick(String nick);
}

